package org.brisling.medical.queryService;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.brisling.authorize.shiro.ShiroSecurityService;
import org.brisling.base.domain.ListDomain;
import org.brisling.base.service.AbstractBaseQueryService;
import org.brisling.common.ConstSysParam;
import org.brisling.common.SysConfig;
import org.brisling.common.exception.DangerousOperatorException;
import org.brisling.common.util.DateUtil;
import org.brisling.common.util.StringUtil;
import org.brisling.medical.domain.BaseOutpatient;
import org.brisling.medical.domain.MedicalRecipel;
import org.brisling.medical.domain.OverlayPrescribe;
import org.brisling.medical.service.OverlayPrescribeService;
import org.brisling.medical.service.RecipelService;
import org.brisling.medical.vo.VOOverlayPrescribe;
import org.brisling.sysman.domain.VOJobUser;
import org.brisling.sysman.queryService.QueryDictionaryService;
import org.brisling.sysman.queryService.QueryPermissionService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;

/**
 * 
 * <p>Title: QueryOverlayPrescribeService</p>
 * <p>Description: 超限处方查询service</p>
 * <p>Company: tongking</p>
 * @author peter fan
 * @since 1.0
 * @version 1.0
 * @date 2016年7月20日 下午10:12:26
 */
@RestController
@RequestMapping("/overlayPrescribe/query")
public class QueryOverlayPrescribeService extends AbstractBaseQueryService<OverlayPrescribe>{
	
	@Autowired
	private OverlayPrescribeService overlayPrescribeService;

	@Autowired
	private QueryDictionaryService dicService;
	
	@Autowired
	private QueryDictionaryService qryDictionaryService;
	
	@Autowired
	private ShiroSecurityService shiroSecurityService;

	@Autowired
	private RecipelService qryService;
	
	@Autowired
	private QueryPermissionService qryPermissionService;
	
	@Autowired
	private QueryBaseOutpatientService qryBaseOutpatient;
	
	private final static String PATIENT_OUT = "1";
	
	
	private final static String type_standard = "5";
	
	private final static String date_standard = "7";
	
	private final static String price_standard = "500";
	
	public QueryOverlayPrescribeService(){
		this.moduleName = ConstSysParam.MODULE_OVERLAY_PRESCRIBE;
		this.subSystem = ConstSysParam.SUBSYSTEM_MEDICAL;
		this.tableName = this.moduleName;
		this.clazz = OverlayPrescribe.class;
		typeMap = this.getTypeMap(clazz);
		orderMap.put("sortedid", null);
	}
	
	@PersistenceContext(unitName=ConstSysParam.SUBSYSTEM_SYSMAN)
	@Autowired
	protected void setEntityManager(EntityManager entityManager){
		this.entityManager = entityManager;		
	}
	
	@RequestMapping("/mainPage.action")
	public ModelAndView getMainPage(){
		return this.getBizMainPage();
	}
	
	/* (non-Javadoc)
	 * @see org.brisling.service.sysman.DepartmentServiceInf#getEditPage()
	 */
	@RequestMapping("/editPage.action")
	public ModelAndView getEditPage(){
		return this.getBizEditPage();
	}
	
	/* (non-Javadoc)
	 * @see org.brisling.service.sysman.DepartmentServiceInf#getEditPage()
	 */
	@RequestMapping("/popPage.action")
	public ModelAndView getPopPage(){
		String userid = shiroSecurityService.getCurrentUserIdentify();
		String _page = "popMain";
		if(!this.isSuperDept(userid,ConstSysParam.QUERY_ALL, "query_medical_all")){
			if(!isManageJob(userid)){				
				_page="popDetail";
			}					
		}
		return new ModelAndView("/medical/medical_overlay_prescribe/"+_page);
	}
	@RequestMapping("/getListDomain.action")
	public ListDomain<OverlayPrescribe> getListDomain(@RequestBody(required=false) String body){		
		
		Map<String,Object> map = this.getBodyParam(body);
		String userid = shiroSecurityService.getCurrentUserIdentify();
		if(!this.isSuperDept(userid,ConstSysParam.QUERY_ALL, "query_medical_all")){			
			addJobDeptConditionToMap(userid,map,"dept_code");			
			//查找科室数据		
		}
		//查询表
		return super.getListDomainByConditionAndOrder(null, map, typeMap, orderMap, true);
		
		
	}
	
	
	/**
	 * 获取部门统计指标
	 * @param body
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@RequestMapping("/getDeptListDomain.action")
	public ListDomain<OverlayPrescribe> getDeptListDomain(@RequestBody(required=false) String body){	
		
		
//		Map<String,Object> map = this.getBodyParam(body);
//		
//		String sql = qryDictionaryService.getSqlByBizcode("overlay_prescribe_dept");
//		String userid = shiroSecurityService.getCurrentUserIdentify();
//		
//		String[] monthday = DateUtil.getMapMonthDay(map);
//		List<String> param = new ArrayList<String>();
//		param.add(monthday[2]);
//		param.add(monthday[0]);
//		param.add(monthday[1]);
//		
//		param = this.getParamArray((Map<String, String>) map.get(ConstSysParam.CONDITION_MAP_KEY), param);
//		
//		String deptwhere = "";
//		if(!this.isSuperDept(userid,ConstSysParam.QUERY_ALL, "query_medical_all")){
//			if(isManageJob(userid)){
//				deptwhere = String.format(" where d.id in (%1$s)",this.getJobDeptidAndAccountDeptids(userid));				
//				
//			}
//		}else{
//			deptwhere = String.format(" where d.id in (%1$s)","-1");
//		}
//		
//		sql = String.format(sql, param.get(0),param.get(1),param.get(2),param.get(3),param.get(4),param.get(5),deptwhere);
//		
//		return super.getAllList(sql);
		Map<String,Object> map = this.getBodyParam(body);
		
		String userid = shiroSecurityService.getCurrentUserIdentify();
		if(!this.isSuperDept(userid,ConstSysParam.QUERY_ALL, "query_medical_all")){
			if(isManageJob(userid)){
				addJobDeptConditionToMap(userid,map,"dept_code_in");
			}else{
				addUsridConditionToMap(userid,map,"doct_code");
			}				
			//查找科室数据		
		}
		
		Map<String,String> paramMap = (Map<String, String>) map.get(ConstSysParam.CONDITION_MAP_KEY);
		if(!paramMap.containsKey("data_type"))
			paramMap.put("data_type", "1");
		
		if(!paramMap.containsKey("patient_type"))
			paramMap.put("patient_type", "1");
		map.put(ConstSysParam.CONDITION_MAP_KEY, paramMap);
		//查询表
		return super.getListDomainByConditionAndOrder(null, map, typeMap, orderMap, true);
		
		
	}
	
	/**
	 * 获取部门统计指标
	 * @param body
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@RequestMapping("/getAllListDomain.action")
	public ListDomain<OverlayPrescribe> getAllListDomain(@RequestBody(required=false) String body){	
		
		
//		Map<String,Object> map = this.getBodyParam(body);
//		
//		String sql = qryDictionaryService.getSqlByBizcode("overlay_prescribe_all");
//		if(sql == null || sql.trim().length()<=0){
//			return new ArrayList<OverlayPrescribe>();
//		}
//		String userid = shiroSecurityService.getCurrentUserIdentify();
//		
//		String[] monthday = DateUtil.getMapMonthDay(map);
//		List<String> param = new ArrayList<String>();
//		param.add(monthday[2]);
//		param.add(monthday[0]);
//		param.add(monthday[1]);
//		
//		param = this.getParamArray((Map<String, String>) map.get(ConstSysParam.CONDITION_MAP_KEY), param);
//		
//		String deptwhere = "";
//		if(!this.isSuperDept(userid,ConstSysParam.QUERY_ALL, "query_medical_all")){
//			if(isManageJob(userid)){
//				deptwhere = String.format(" where d.id in (%1$s)",this.getJobDeptidAndAccountDeptids(userid));				
//				sql = qryDictionaryService.getSqlByBizcode("overlay_prescribe_dept");
//			}else{
//				deptwhere = String.format(" where u.id = '%1$s'",userid);
//			}
//		}else{
//			deptwhere = String.format(" where d.id in (%1$s)","-1");
//		}
//		
//		sql = String.format(sql, param.get(0),param.get(1),param.get(2),param.get(3),param.get(4),param.get(5),deptwhere);
//		
//		return super.getAllList(sql);
		Map<String,Object> map = this.getBodyParam(body);
		
		String userid = shiroSecurityService.getCurrentUserIdentify();
		Map<String,String> paramMap = (Map<String, String>) map.get(ConstSysParam.CONDITION_MAP_KEY);
		
		String dataType = "1";
		if(!this.isSuperDept(userid,ConstSysParam.QUERY_ALL, "query_medical_all")){
			if(isManageJob(userid)){
				String deptids = this.getJobDeptidAndAccountDeptids(userid);
				paramMap.put("dept_code_in", deptids);
				dataType = "2";
			}else{
				
				paramMap.put("doct_code", userid);
				dataType = "3";
			}
			//查找科室数据		
		}else{
			paramMap.put("dept_code_in", "-1");
			dataType = "1";
		}		
		paramMap.put("data_type", dataType);
		
		map.put(ConstSysParam.CONDITION_MAP_KEY, paramMap);
		//查询表
		return super.getListDomainByConditionAndOrder(null, map, typeMap, orderMap, true);
		
		
	}
	
	
	/**
	 * 获取部门统计指标
	 * @param body
	 * @return
	 */
	
	public List<OverlayPrescribe> getList(String monitor_year_month){
		
		String sql = String.format("select * from %1$s where monitor_year_month='%2$s'",this.tableName,monitor_year_month); 
		//查询表
		return super.getAllList(sql);
		
	}
	
	/**
	 * 查询实时数据
	 * @param body
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@RequestMapping("/getRealTimeListDomain.action")
	public ListDomain<OverlayPrescribe> getRealTimeListDomain(@RequestBody(required=false) String body){		
		Map<String,Object> paramMap = this.getBodyParam(body);
		
		String[] monitorDate = super.getMonitorDate(paramMap);
		if(monitorDate!=null && monitorDate.length>1){			
			
			String start = monitorDate[0];
			String end = monitorDate[1];
			String year_month = monitorDate[2];
			List<String> param = new ArrayList<String>();
			param.add(year_month);
			param.add(start);
			param.add(end);
			
			param = this.getParamArray((Map<String, String>) paramMap.get(ConstSysParam.CONDITION_MAP_KEY), param);
			List<OverlayPrescribe> inlists =countBaseMedicalOutpatient (param);
			
			if(inlists!=null){
				
				overlayPrescribeService.save(inlists);
				
				return super.getListDomainByConditionAndOrderUseTable(body, typeMap, orderMap,true);
			}					
			
		}
		//查询表
		
		return new ListDomain<OverlayPrescribe>();
	}
	
	
	public void autoTask(Integer year,Integer month){
		this.autoMonitor(year, month);
		this.autoUpdateStandard(year, month);
	}
	/**
	 * 执行自动统计
	 */
	public void autoMonitor(Integer year,Integer month){
		String[] yestodayMonth = null;
		if(year ==null || month ==null)
			yestodayMonth = DateUtil.getYestodayMonthDay();
		else{
			yestodayMonth = DateUtil.getMonthDay(year, month);
		}
		
		List<String> param = new ArrayList<String>();
		param.add(yestodayMonth[2]);
		param.add(yestodayMonth[0]);
		param.add(yestodayMonth[1]);

		
		String back = SysConfig.getConfig("overlayPrescribeStander");
		String[] arguLB = null;
		if(back != null){
			arguLB = back.split(",");
		}
		int index = 0;
		Map<String, String> typePara = new HashMap<>();
		if(arguLB != null && arguLB.length > 0){
			if(arguLB[0] != null && (!arguLB[0].equals("")) && Long.valueOf(arguLB[0]) >= 0){
				typePara.put("drug_type_standard", arguLB[0]);
			}
			if(arguLB.length > 1){
				if(arguLB[1] != null && (!arguLB[1].equals("")) && Long.valueOf(arguLB[1]) >= 0){
					typePara.put("drug_price_standard", arguLB[1]);
				}
				if(arguLB.length > 2){
					if(arguLB[2] != null && (!arguLB[2].equals("")) && Long.valueOf(arguLB[2]) >= 0){
						typePara.put("drug_date_standard", arguLB[2]);
					}
				}
			}
		}
		param = this.getParamArray(typePara, param);
		List<OverlayPrescribe> inlists =countBaseMedicalOutpatient (param);
		
		//查询已有数据
		List<OverlayPrescribe> oldLists = this.getList(yestodayMonth[2]);
		LinkedHashMap<String,OverlayPrescribe> lmap = new LinkedHashMap<String,OverlayPrescribe>();
		
		//将已有值存入临时map
		if(oldLists!=null && oldLists.size()>0){
			
			for(int i=0;i<oldLists.size();i++){
				OverlayPrescribe b = oldLists.get(i);
				String _key = null;
				if(b.getDoct_code()==null ){
					_key = b.getDept_code()+"-"+b.getPatient_type();
				}else{
					_key = b.getDept_code() +"-" + b.getDoct_code()+"-"+b.getPatient_type();
				}
				lmap.put(_key, b);
			}
		}
		
		
		if(inlists!=null ){
			
			if(lmap!=null && lmap.size()>0){
				for(int i=0;i<inlists.size();i++){
					OverlayPrescribe b = inlists.get(i);
					String _key = null;
					if(b.getDoct_code()==null ){
						_key = b.getDept_code()+"-"+b.getPatient_type();
					}else{
						_key = b.getDept_code() +"-" + b.getDoct_code()+"-"+b.getPatient_type();
					}
					if(lmap.containsKey(_key)){
						inlists.get(i).setId(lmap.get(_key).getId());
					}
				}
			}
			
			overlayPrescribeService.save(inlists);	
			
		}	
	}
	
	public void autoUpdateStandard(Integer year,Integer month){
		String[] yestodayMonth = null;
		if(year ==null || month ==null)
			yestodayMonth = DateUtil.getYestodayMonthDay();
		else{
			yestodayMonth = DateUtil.getMonthDay(year, month);
		}
		overlayPrescribeService.updateStandard(yestodayMonth[2]);
	}
	
	
	
	/**
	 * 统计基本药物住院缓存信息
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	public List<OverlayPrescribe> countBaseMedicalOutpatient(List<String> condition){
		
			
		
		String[] sqlids = {"overlay_prescribe_all","overlay_prescribe_dept","overlay_prescribe_doct"};
		//统计全院数据-住院
		
		List<String> sqls = new ArrayList<String>();
		for(int i = 0;i<sqlids.length;i++){
			String sql = qryDictionaryService.getSqlByBizcode(sqlids[i]);
			if(sql!=null){
				sqls.add(sql);
			}
		}
		//所有数据
		List<OverlayPrescribe> lists = monitorCount(condition,sqls);		
		
		return lists;
		
	}	
	
	
	private List<OverlayPrescribe> monitorCount(List<String> condition,
			List<String> sqls){		
		
		//所有数据
		List<OverlayPrescribe> lists = new ArrayList<OverlayPrescribe>();
		
		if(condition!=null && condition.size()>0){
			
			for(int i=0;i<sqls.size();i++){
				String sql = sqls.get(i);
				
				sql = String.format(sql, condition.get(0),condition.get(1),condition.get(2),condition.get(3),condition.get(4),condition.get(5));
				List<OverlayPrescribe> allLists = getAllList(sql);
				if(allLists!=null && allLists.size()>0)
					lists.addAll(allLists);
			}
		}		
		return lists;
	}
	
	/**
	 * 根据用户id查找用户信息
	 * @param id	用户id
	 * @return 用户信息记录 
	 */
	@RequestMapping("/getOne.action")
	public OverlayPrescribe getOne(@RequestParam(value="id",required=false) Long id){
				
		return super.getOne(id);
	}
	
	/**
	 * 根据用户唯一编码查找用户信息
	 * @param identify	用户唯一编码
	 * @return 用户信息记录
	 */
	@RequestMapping("/getOneByIdentify.action")
	public OverlayPrescribe getOneByIdentify(@RequestParam(value="identify",required=false) String identify){	
		return super.getOneByIdentify(identify);
	}
	
	@SuppressWarnings("unused")
	private Map<String,String> getParamMap(Map<String,String> paramMap){
		
		if(!paramMap.containsKey("data_type"))
			paramMap.put("data_type", "1");
		
		if(!paramMap.containsKey("patient_type"))
			paramMap.put("patient_type", PATIENT_OUT);
		
		if(!paramMap.containsKey("drug_type_standard"))
			paramMap.put("drug_type_standard", type_standard);
		
		if(!paramMap.containsKey("drug_price_standard"))
			paramMap.put("drug_price_standard", price_standard);
		
		if(!paramMap.containsKey("drug_date_standard"))
			paramMap.put("drug_date_standard", date_standard);
		
		return paramMap;
	}
	
	private List<String> getParamArray(Map<String,String> paramMap,List<String> retVal){
		if(retVal==null)retVal = new ArrayList<String>();
		
		if(paramMap !=null && paramMap.containsKey("drug_type_standard")){
			retVal.add(paramMap.get("drug_type_standard"));			
		}else{
			retVal.add(type_standard);
		}
		
			
		
		if(paramMap !=null && paramMap.containsKey("drug_date_standard")){
			retVal.add(paramMap.get("drug_date_standard"));			
		}else{
			retVal.add(date_standard);
		}
		if(paramMap !=null && paramMap.containsKey("drug_price_standard")){
			retVal.add(paramMap.get("drug_price_standard"));
			
		}else{
			retVal.add(price_standard);
		}	
		return retVal;
	}
	
	//OverlayPrescribe
	/**
	 * 超限处方异常数据
	 * @return
	 */	
	@RequestMapping("/getOverlayPrescribeAbnormal.action")
	public List<VOOverlayPrescribe> getOverlayPrescribeAbnormal(@RequestBody(required=false) String body){		
		
		String sql = dicService.getSqlByBizcode("medical_overlay_prescribe");
		if(sql == null || sql.length() < 1){
			return null;
		}
		
		Map<String,Object> map = this.getBodyParam(body);
		String userid = shiroSecurityService.getCurrentUserIdentify();
		if(!this.isSuperDept(userid,ConstSysParam.QUERY_ALL, ConstSysParam.QUERY_MEDICAL_ALL)){			
			addJobDeptConditionToMap(userid,map,"dept_code");			
			//查找科室数据		
		}
		
		if(map.containsKey(ConstSysParam.CONDITION_MAP_KEY)){
			Map<String,Object> conditionMap = (Map<String, Object>) map.get(ConstSysParam.CONDITION_MAP_KEY);
			
			//判断必备时间段查询条件是否存在
			if(null !=conditionMap 
					&& conditionMap.containsKey("start_time")
					&& conditionMap.containsKey("end_time"))
			sql = StringUtil.formatSql(sql, conditionMap);
			
			try {
				return super.getClassedAllList(sql, VOOverlayPrescribe.class);
			} catch (DangerousOperatorException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		
		
		 	
		return null;
	}
	
	@Autowired
	private QueryBaseOutpatientService qryBaseOutpatientService;
	
	
	/**
	 * 超限处方单处方基本信息查询
	 * @return
	 */	
	@RequestMapping("/getOutpatientListDomain.action")
	public ListDomain<BaseOutpatient> getOutpatientListDomain(@RequestBody(required=false) String body){		
		
		return qryBaseOutpatientService.getListDomain(body);
	}
		
	

	public List<VOOverlayPrescribe> autoInsertData(String time){
		List<VOOverlayPrescribe>  lists=getOverlayPrescribeAbnormal(time);
		String Bizcode = qryPermissionService.getBizFlowConfig("recipel").getFullBizcode();
		String flowid = qryPermissionService.getBizFlowConfig("recipel").getFlowid();
		
		if(lists!=null){
			int i = 0;
			for(VOOverlayPrescribe a:lists){
				//System.out.println("\n--------------------------------got it----------------------------------");
				MedicalRecipel sendObj = new MedicalRecipel();
				sendObj.setId(null);
				sendObj.setInquiry_item("超限处方");
				sendObj.setInquiry_item_code("53");
				sendObj.setInquiry_item_sub("医生超限处方");
				sendObj.setDoct_name(a.getDoct_name());
				sendObj.setDoct_id(a.getDoct_code());
				sendObj.setDoct_depart_name(a.getDept_name());
				sendObj.setPatient_type("1");
				List<VOJobUser> jobUsr = SysConfig.getJobUserByDept("20", a.getDept_code().toString());
				if(jobUsr == null || jobUsr.size() < 1){
					System.out.println("ERROR:找不到科主任, No 科主任 found dept_code:" + a.getDept_code() + " dept_name " + a.getDept_name() + " outpatient_number " + a.getOutpatient_number());
//					continue;
				}
//				sendObj.setDoct_name(jobUsr.get(0).getDoct_name());
//				sendObj.setDoct_id(jobUsr.get(0).getDoct_code().toString());
				//sendObj.setDeptName(a.getDept_name());
				//sendObj.setDeptid(a.getDept_code());
				sendObj.setInquiry_item_subcode("172");
				sendObj.setDoct_depart_code(a.getDept_code());
				
				Date _date = DateUtil.addDate(a.getFeeat(),1);
				sendObj.setInquiry_date(_date);
				sendObj.setCreateTime(_date);
				sendObj.setPatient_name(a.getPatient_name());
				sendObj.setPatient_type("1");
				sendObj.setZyhm(a.getMzhm());
				sendObj.setPatient_id(a.getOutpatient_number());
				sendObj.setOuthospitalat(a.getFeeat());
				
				sendObj.setFeeat(a.getFeeat());
				String reson = "";
				int cou = 0;
				if(a.getDrug_type_overlay() == 1){
					reson += "药品数目超过标准,";
					cou++;
				}
				if(a.getDrug_price_overlay() == 1){
					reson += "药品总价格超过标准,";
					cou++;
				}
				if(a.getDrug_date_overlay() == 1){
					reson += "处方用药周期超过标准,";
					cou++;
				}
				reson += "请对";
				if(cou == 0){
					continue;
				}
				if(cou == 1){
					reson += "这个";
				}
				else{
					reson += "这些";
				}
				reson += "异常情况做出合理的解释。";
				sendObj.setInquiry_reason(reson);
				sendObj.setFlowid(flowid);
				sendObj.setFullBizcode(Bizcode);
				//设置异常实体信息
				String id =SysConfig.getConfig("inquiryUserId");
				qryService.completeTask(id, sendObj);
			}
		}
		return lists;
	}
}




/*
select * from V_medical_inpatient 

select f.* from 
    (select patient_id,drug_code ,count(patient_id)fcount, yzlx from v_medical_inpatient_fee
    where patient_id = 169770 
    group by patient_id,drug_code) f
left join
    (select patient_id,drug_code,count(patient_id)icount from V_MEDICAL_INPATIENT_ITEM
    where patient_id = 169770
    group by patient_id,drug_code)i
on f.patient_id = i.patient_id and f.drug_code = i.drug_code
where f.fcount > i.icount



select * from V_medical_inpatient 


select big.* from(
  select z.* from v_medical_inpatient z where z.OUTHOSPITALAT >= to_date('2016-07-29', 'yyyy-mm-dd') and z.OUTHOSPITALAT <= to_date('2016-07-29', 'yyyy-mm-dd')
) big
left join (
  select item.patient_id, item.drug_code, count(item.patient_id) smlCount from v_medical_inpatient_item item left join
  v_medical_inpatient zz on item.patient_id = h.patient_id where zz.OUTHOSPITALAT >= to_date('2016-07-29', 'yyyy-mm-dd') and zz.OUTHOSPITALAT <= to_date('2016-07-29', 'yyyy-mm-dd') and item.yzlx = 1
  group by item.patient_id, item.drug_code
) sml
on big.patient_id = sml.patient_id
left join(
  select fee.patient_id, fee.drug_code, count(fee.patient_id) slrCount from v_medical_inpatient_fee fee
  group by fee.patient_id, fee.drug_code
) slr
on slr.patient_id = sml.patient_id
where sml.smlCount != slr.slrCount;


select big.* from(
  select z.* from v_medical_inpatient z where z.OUTHOSPITALAT >= to_date('2016-07-29', 'yyyy-mm-dd') and z.OUTHOSPITALAT <= to_date('2016-07-29', 'yyyy-mm-dd')
) big
left join (
  select item.patient_id, item.drug_code, count(item.patient_id) smlCount from v_medical_inpatient_item item left join
  v_medical_inpatient zz on item.patient_id = zz.patient_id where zz.OUTHOSPITALAT >= to_date('2016-07-29', 'yyyy-mm-dd') and zz.OUTHOSPITALAT <= to_date('2016-07-29', 'yyyy-mm-dd') and item.yzlx = 1
  group by item.patient_id, item.drug_code
) sml
on big.patient_id = sml.patient_id
left join(
  select fee.patient_id, fee.drug_code, count(fee.patient_id) slrCount from v_medical_inpatient_fee fee left join
  v_medical_inpatient zzz on fee.patient_id = zzz.patient_id where zzz.OUTHOSPITALAT >= to_date('2016-07-29', 'yyyy-mm-dd') and zzz.OUTHOSPITALAT <= to_date('2016-07-29', 'yyyy-mm-dd')
  group by fee.patient_id, fee.drug_code
) slr
on slr.patient_id = sml.patient_id
where sml.smlCount != slr.slrCount;



*****************************************************************************************
select big.* from(
  select z.* from v_medical_inpatient z where z.OUTHOSPITALAT >= to_date('2016-07-29 00:00:00', 'yyyy-MM-dd hh24:mi:ss') 
    and z.OUTHOSPITALAT <= to_date('2016-07-29 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
) big
left join (
  select item.patient_id, item.drug_code, count(item.patient_id) smlCount from v_medical_inpatient_item item left join
  v_medical_inpatient zz on item.patient_id = zz.patient_id where zz.OUTHOSPITALAT >= to_date('2016-07-29 00:00:00', 'yyyy-MM-dd hh24:mi:ss') 
    and zz.OUTHOSPITALAT <= to_date('2016-07-29 23:59:59', 'yyyy-MM-dd hh24:mi:ss') and item.yzlx = 1
  group by item.patient_id, item.drug_code
) sml
on big.patient_id = sml.patient_id
left join(
  select fee.patient_id, fee.drug_code, count(fee.patient_id) slrCount from v_medical_inpatient_fee fee left join
  v_medical_inpatient zzz on fee.patient_id = zzz.patient_id where zzz.OUTHOSPITALAT >= to_date('2016-07-29 00:00:00', 'yyyy-MM-dd hh24:mi:ss') 
  and zzz.OUTHOSPITALAT <= to_date('2016-07-29 23:59:59', 'yyyy-MM-dd hh24:mi:ss')
  group by fee.patient_id, fee.drug_code
) slr
on slr.patient_id = sml.patient_id
where sml.smlCount != slr.slrCount;
****************************************************************************************
select f.* from 
    (select patient_id,drug_code ,count(
        drug_code
    )fcount from V_MEDICAL_INPATIENT_ITEM
    where YZLX =1  
    group by patient_id,drug_code) f
left join
    (select patient_id,drug_code,count(patient_id)icount from V_MEDICAL_INPATIENT_ITEM
    where patient_id = 169770
    group by patient_id,drug_code)i
on f.patient_id = i.patient_id and f.drug_code = i.drug_code
where f.fcount > i.icount
*/
